﻿Imports System.ComponentModel
Imports System.Data.Common
Imports WRDB.Project

''' <summary>
''' Import data previously acquired from ICIS using specialized BOXI queries which extract data appropriate for WRDB
''' See "ICIS-WRDB Field Mapping.xlsx" spreadsheet for details on field mapping
''' This class library has no WRDB Forms references so can be used with web project if desired
''' </summary>
''' <remarks></remarks>
Public Class clsImportICIS

    Private _ImportURL As String
    Private _Rebuild As Boolean 'if true, will purge all files and build from scratch, otherwise data file will be update from 1/1/2008 onward
    Private WithEvents _Project As WRDB.Project.clsProject

    Private dictPCodes As New Generic.Dictionary(Of String, String) 'to avoid duplicate pcodes when shorten them
    Private lstWaterBodyTypes As New Generic.List(Of String) 'filled when import stations, added to validation table

    ''' <summary>
    ''' Is raised during long operations (e.g., import/export) so caller can report using UI
    ''' </summary>
    ''' <param name="Value">Current value (must alway initialize with 0 and finalize with Value=MaxValue)</param>
    ''' <param name="MaxValue">Maximum value</param>
    ''' <param name="ProgressText">Text to display</param>
    Public Event Progress(ByRef DB As clsDB, ByVal Value As Long, ByVal MaxValue As Long, ByVal ProgressText As String)

    Public Sub New(ByVal WRDBProject As WRDB.Project.clsProject)
        _Project = WRDBProject
        _ImportURL = "C:\Project Files\WE09002_Tt_EPDRiv1\ICIS-Georgia Update\"
        _Rebuild = False
        '_ImportURL = "C:\Project Files\WE09002_Tt_EPDRiv1\ICIS-Alabama\"
        '_Rebuild = True
    End Sub

    Public Property Rebuild As Boolean
        Get
            Return _Rebuild
        End Get
        Set(ByVal value As Boolean)
            _Rebuild = value
        End Set
    End Property

    ''' <summary>
    ''' URL or folder containing all import files (forced to end in slash)
    ''' </summary>
    Public Property ImportURL() As String
        Get
            If Not UrlIsFTP() Then 'is file; make sure directory exists
                If Not My.Computer.FileSystem.DirectoryExists(_ImportURL) Then Throw New System.IO.DirectoryNotFoundException(String.Format("ICIS Import directory not found: '{0}'", _ImportURL))
            End If
            Return _ImportURL
        End Get
        Set(ByVal value As String)
            If UrlIsFTP() Then
                If Not value.EndsWith("/") Then value &= "/"
            Else
                If Not value.EndsWith("\") Then value &= "\"
                If Not My.Computer.FileSystem.DirectoryExists(value) Then My.Computer.FileSystem.CreateDirectory(value)
            End If
            _ImportURL = value
        End Set
    End Property

    Private Function UrlIsFTP() As Boolean
        Return _ImportURL.StartsWith("//")
    End Function

    Private Function OpenCSV(ByVal FileName As String, Optional ByRef NumRecords As Long = 0) As LumenWorks.Framework.IO.Csv.CsvReader
        Dim fn As String = ImportURL & FileName & ".csv"
        If Not My.Computer.FileSystem.FileExists(fn) Then Throw New System.IO.FileNotFoundException(String.Format("ICIS Import file not found: '{0}'", fn))
        Dim sr As New System.IO.StreamReader(fn)
        'cannot exactly determine the number of records, but read the first 100 lines and get the average length of each record to estimate
        sr.ReadLine()
        Dim NumBytes As Integer = 0
        For i As Integer = 1 To 100
            NumBytes += sr.ReadLine.Length + 2
            If sr.EndOfStream Then Exit For
        Next
        NumRecords = CInt(sr.BaseStream.Length / (NumBytes / 100))
        sr.Close()
        sr = New System.IO.StreamReader(fn)
        Dim csv As New LumenWorks.Framework.IO.Csv.CsvReader(sr, True, ",", """", "|0", "", True, 50000)
        Return csv
    End Function

    Private Function ImportStations() As Boolean
        RaiseEvent Progress(_Project.DB, 0, 100, "Importing ICIS stations to temporary file...")

        'ICIS uses FIPS codes like AL001 rather than the conventional 1001; put in array so can get index of state abbreviation
        Dim StateAbbrev() As String = {"", "AL", "AK", "", "AZ", "AR", "CA", "", "CO", "CT", "DE", "DC", "FL", "GA", "", "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA", "", "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA", "", "WA", "WV", "WI", "WY"}
        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            'routine expects file exported from ICIS to have these column headings
            Dim Fieldnames As String() = {"NPDES ID", "Perm Feature ID", "Version Nmbr", "Permit Name", "Perm Feature Desc", "Latitude in Decimal Degrees", "Longitude in Decimal Degrees", "Issue Date", "Expiration Date", "Curr. Major Minor Status", "FIPS County Code", "State Water Body", "State Water Body Name", "Number of Limits", "Number of Received DMR Values", "Number of DMR Violations", "Permit Status Desc"}
            Dim NumRecords As Integer = 0
            csv = OpenCSV("Stations", NumRecords)
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\Stations.txt"
            With csv
                'check that fieldnames are correct...
                Dim ar() As String = .GetFieldHeaders
                For i As Integer = 0 To csv.FieldCount - 1
                    If ar(i) <> Fieldnames(i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                Next

                'create tab-delimited text file suitable for direct import
                sw = New IO.StreamWriter(ImportFilename)
                sw.WriteLine("STATION_ID\tSTATION_NAME\tSTATION_TYPE\tLATITUDE\tLONGITUDE\tREMARKS\tOTHER\tBASIN_NAME\tFIPS_CODE\tSTATE\tCOUNTY_NAME\tHUC\tREACH_ID\tAGENCY\tECOREGION\tDATA_FREQ\tELEVATION\tRIVER_MILE\tWaterBodyType".Replace("\t", vbTab))

                'get lookup list of county names from FIPSCodes table
                Dim dictCounty As New Generic.Dictionary(Of Integer, String)
                Dim dr As DbDataReader = _Project.DB.ExecuteReader("SELECT FIPS_Code,County_Name FROM FIPSCodes") ' WHERE State='GA'")
                While dr IsNot Nothing AndAlso dr.HasRows AndAlso dr.Read
                    Dim FIPSCode As Integer = TestNull(dr.GetValue(0), 0)
                    Dim CountyName As String = TestNull(dr.GetValue(1), "")
                    If FIPSCode <> 0 And CountyName <> "" Then dictCounty.Add(FIPSCode, CountyName)
                End While
                dr.Close()

                Dim lstMissingHUCs As New Generic.List(Of String)
                lstWaterBodyTypes.Clear()

                While .ReadNextRecord
                    If .Item(0) <> "" Then
                        Dim stationid As String = String.Format("{0}{1}-{2}", .Item(0), IIf(.Item(1) = "", "", "+" & .Item(1)), .Item(2))
                        Dim stationname As String = StrConv(.Item(3), VbStrConv.ProperCase)
                        Dim outfalldesc As String = StrConv(.Item(4), VbStrConv.ProperCase)
                        Dim latitude As String = .Item(5)
                        Dim longitude As String = .Item(6)
                        Dim issuedate As String = .Item(7)
                        Dim expirationdate As String = .Item(8)
                        Dim status As String = .Item(9)
                        Dim fipscode As String = .Item(10) '.Replace("GA", "13")
                        Dim state As String = ""
                        Dim county As String = ""
                        If fipscode <> "" Then
                            state = fipscode.Substring(0, 2)
                            fipscode = Array.IndexOf(StateAbbrev, state) & fipscode.Substring(2, 3) 'convert AL001 to 1001
                            dictCounty.TryGetValue(Val(fipscode), county)
                        End If
                        Dim huc As String = .Item(11)
                        Dim waterbody As String = .Item(12)
                        Dim numlimits As String = .Item(13)
                        Dim numvalues As String = .Item(14)
                        Dim numviolations As String = .Item(15)
                        Dim waterbodytype As String = .Item(16)
                        If Not String.IsNullOrEmpty(waterbodytype) AndAlso Not lstWaterBodyTypes.Contains(waterbodytype) Then lstWaterBodyTypes.Add(waterbodytype)

                        Dim reach As String = "", basin As String = "", ecoregion As String = ""

                        '10/5/10: no longer try to parse the waterbody field; store this in REACH and get BASIN from HUC code (also found lots of 4-digit codes indicating RB)
                        'ParseWaterbody(waterbody.ToUpper, reach, basin)
                        reach = waterbody

                        'if four-digit code, set river basin HUC code

                        If huc = "0313" Then
                            huc = "030601000000"
                        ElseIf huc = "0314" Then
                            huc = "030602000000"
                        ElseIf huc = "315" Or huc = "0315" Then
                            huc = "030701010000"
                        ElseIf huc = "0317" Then
                            huc = "030701060000"
                        ElseIf huc = "0316" Then
                            huc = "030701000000"
                        ElseIf huc = "0319" Then
                            huc = "030702040000"
                        ElseIf huc = "0318" Then
                            huc = "030702000000"
                        ElseIf huc = "0322" Then
                            huc = "031101030000"
                        ElseIf huc = "0321" Then
                            huc = "031100000000"
                        ElseIf huc = "0330" Then
                            huc = "031300010000"
                        ElseIf huc = "0329" Or huc = "329" Then '05 - 11
                            huc = "031300000000"
                        ElseIf huc = "0334" Then
                            huc = "031501080000"
                        ElseIf huc = "0335" Then
                            huc = "031501000000"
                        ElseIf huc = "0408" Then
                            huc = "060000000000"
                        Else
                            If huc.Trim <> "" Then lstMissingHUCs.Add(huc & vbTab & stationid & vbTab & waterbody)
                        End If

                        If huc.StartsWith("030601") Then
                            basin = "SAVANNAH"
                        ElseIf huc.StartsWith("030602") Then
                            basin = "OGEECHEE"
                        ElseIf huc = "03140315" Then
                            basin = "OGEECHEE/OCONEE"
                        ElseIf huc.StartsWith("03070101") Or huc.StartsWith("03070102") Then
                            basin = "OCONEE"
                        ElseIf huc.StartsWith("03070106") Or huc.StartsWith("03070107") Then
                            basin = "ALTAMAHA"
                        ElseIf huc.StartsWith("030701") Then
                            basin = "OCMULGEE"
                        ElseIf huc.StartsWith("03070204") Then
                            basin = "ST. MARYS"
                        ElseIf huc.StartsWith("030702") Then
                            basin = "SATILLA"
                        ElseIf huc.StartsWith("0312") Or huc.StartsWith("03110103") Then
                            basin = "OCHLOCKONEE"
                        ElseIf huc.StartsWith("0311") Then
                            basin = "SUWANNEE"
                        ElseIf huc.StartsWith("03130001") Or huc.StartsWith("03130002") Or huc.StartsWith("03130003") Or huc.StartsWith("03130004") Then
                            basin = "CHATTAHOOCHEE"
                        ElseIf huc.StartsWith("031300") Then '05 - 11
                            basin = "FLINT"
                        ElseIf huc.StartsWith("03150108") Then
                            basin = "TALLAPOOSA"
                        ElseIf huc.StartsWith("031501") Then
                            basin = "COOSA"
                        ElseIf huc.StartsWith("06") Then
                            basin = "TENNESSEE"
                        Else
                            If huc.Trim <> "" Then lstMissingHUCs.Add(huc & vbTab & stationid & vbTab & waterbody)
                        End If

                        '11/28/10: redefine so that status (major/minor) stored in stationtype and outfalldesc stored in other

                        Dim s As String = String.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}-{6}\t{7}\t{8}\t{9}\t{10}\t{11}\t{12}\t{13}\tGAEPD\t{14}\t{15}\t{16}\t{17}\t{18}",
                                                   stationid,
                                                   stationname,
                                                   status,
                                                   latitude,
                                                   longitude,
                                                   issuedate.Replace(" 00:00:00", ""), expirationdate.Replace(" 00:00:00", ""),
                                                   outfalldesc,
                                                   basin,
                                                   fipscode,
                                                   state,
                                                   county,
                                                   huc,
                                                   reach,
                                                   ecoregion,
                                                   numvalues,
                                                   numlimits,
                                                   numviolations,
                                                   waterbodytype
                                                  ).Replace(vbTab, " ").Replace(vbCr, ";").Replace(vbLf, "") 'strip off tabs and returns within fields, then add them between fields
                        sw.WriteLine(s.Replace("\t", vbTab))
                    End If
                    RaiseEvent Progress(_Project.DB, .CurrentRecordIndex, NumRecords, "Processing ICIS Station record " & .CurrentRecordIndex + 1)
                End While
                If lstMissingHUCs.Count > 0 Then
                    lstMissingHUCs.Sort()
                    Debug.Print("Missing HUCs:")
                    For Each s As String In lstMissingHUCs
                        Debug.Print(s)
                    Next
                End If
            End With
            sw.Close()
            If Not _Project.ImportTable("Stations", ImportFilename, False) Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "Stations"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' Try to parse waterbody field into river name and river basin name based on visual clues
    ''' </summary>
    Private Sub ParseWaterbody(ByVal waterbody As String, ByRef reach As String, ByRef basin As String)
        reach = waterbody
        basin = ""
        If waterbody.Contains("-") Then 'assume this is how they are separated
            Dim i As Integer = waterbody.LastIndexOf("-") 'last part always is river basin
            reach = waterbody.Substring(0, i).Trim
            basin = waterbody.Substring(i + 1).Trim
        Else
            If waterbody.EndsWith(" RB") Or waterbody.EndsWith(" BASIN") Then
                basin = waterbody
                reach = ""
            End If
        End If
        If basin.EndsWith(" RB") And basin.Length > 3 Then basin = basin.Substring(0, basin.Length - 3)
        If basin.EndsWith(" RV BASIN") And basin.Length > 9 Then basin = basin.Substring(0, basin.Length - 9)
        If basin.EndsWith(" RIVER BASIN") And basin.Length > 12 Then basin = basin.Substring(0, basin.Length - 12)
        If basin.EndsWith(" BASIN") And basin.Length > 6 Then basin = basin.Substring(0, basin.Length - 6)
        If basin = "SAV" Then basin = "SAVANNAH"
        If basin = "CHATT" Then basin = "CHATTAHOOCHEE"
        If basin = "TENN" Then basin = "TENNESSEE"
        reach = StrConv(reach, VbStrConv.ProperCase)
        basin = StrConv(basin, VbStrConv.ProperCase)
    End Sub

    Private Function ImportPCodes() As Boolean
        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            'routine expects file exported from ICIS to have these column headings
            Dim Fieldnames As String() = {"Parameter Code", "Parameter Desc", "Parameter Unit Group Desc", "Parameter SNC Group", "State Code", "Limit Unit Short Desc", "Std. Concentration Unit Short Desc", "Std. Quantity Unit Short Desc"}
            csv = OpenCSV("PCodes")
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\PCodes.txt"
            With csv
                'check that fieldnames are correct...
                Dim ar() As String = .GetFieldHeaders
                For i As Integer = 0 To csv.FieldCount - 1
                    If ar(i) <> Fieldnames(i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                Next

                'create tab-delimited text file suitable for direct import
                sw = New IO.StreamWriter(ImportFilename)
                sw.WriteLine("PCODE\tANAL_NAME\tUNITS\tTEST_METHOD".Replace("\t", vbTab))

                While .ReadNextRecord
                    Dim pcode As String = .Item(0)
                    If pcode <> "" Then
                        'parse to determine units to use for concentrations and loads where format is "Q:load units;C:conc units"
                        'if only one, assign to 5-digit pcode; if both present, assign load to 5-digit code and append _LOAD

                        'when importing data, form pcode by looking at 1st character of DMR Value Type Code (Q1, Q2, C1, C2, C3) and
                        'if is C, just use 5 digit code; if Q then look in lstPCodes for xxxxx_LOAD; if not found, just use number 
                        '(this is because some have only Q units like flow, and don't want to append LOAD unnecessarily)

                        'update 11/28/10: contrary to earlier assertions, it IS possible for limit and reported values to have
                        'different units than those suggested by the PCode units. ICIS Query was revised to extract unique PCodes and
                        'units for all limit values, as well as "Standard" units for concentration and quantity; will create pcodes for each
                        'Storet code (e.g., 00310); if "Limit Unit Short Desc" is same as "Std. Concentration Unit Short Desc" then will not
                        'append anything; otherwise will append units (e.g., 00310LB/D, 00310KG/D, etc.) (will no longer append _LOAD)

                        Dim name As String = .Item(1)
                        Dim method As String = .Item(3) 'Parameter SNC Group to determine how to report exceedances
                        Dim units As String = .Item(5)
                        Dim concUnits As String = .Item(6)
                        Dim loadUnits As String = .Item(7)
                        Dim pcodeunits As String = pcode
                        If units <> concUnits Then pcodeunits &= "_" & Strings.Left(units, 4).ToUpper
                        Dim cnt As Integer = 2
                        While dictPCodes.ContainsValue(pcodeunits)
                            pcodeunits = pcodeunits.Substring(0, pcodeunits.Length - 1) & cnt
                            cnt += 1
                        End While
                        dictPCodes.Add(pcode & units, pcodeunits)
                        sw.WriteLine(String.Format("{0}\t{1}\t{2}\t{3}", pcodeunits, name, units, method).Replace("\t", vbTab))
                    End If
                End While
            End With
            sw.Close()
            If Not _Project.ImportTable("PCodes", ImportFilename, False) Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "PCodes"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
        End Try
    End Function

    Private Function ImportRCodes() As Boolean
        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            'routine expects file exported from ICIS to have these column headings
            'NOTE: this report has results from both query on unique NODI values and DMR Value Qualifier Codes, so must skip over second set of column headings
            Dim Fieldnames As String() = {"NODI Code", "NODI Desc", "State Code"}
            csv = OpenCSV("RCodes")
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\RCodes.txt"
            With csv
                'check that fieldnames are correct...
                Dim ar() As String = .GetFieldHeaders
                For i As Integer = 0 To csv.FieldCount - 1
                    If ar(i) <> Fieldnames(i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                Next

                'create tab-delimited text file suitable for direct import
                sw = New IO.StreamWriter(ImportFilename)
                sw.WriteLine("RCODE\tDESCRIPTION\tCALC_STATS\tNONDETECT_PCODE\tNONDETECT_RESULT\tDETECT_MULT".Replace("\t", vbTab))

                While .ReadNextRecord
                    If .Item(0) <> "" And .Item(0) <> "=" And Not .Item(0).StartsWith("DMR") Then
                        sw.WriteLine(String.Format("{0}\t{1}\tFalse\tTrue\tFalse\t0.5", .Item(0), .Item(1)).Replace("\t", vbTab))
                    End If
                End While
            End With
            sw.Close()
            If Not _Project.ImportTable("RCodes", ImportFilename, False) Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "RCodes"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
        End Try
    End Function

    Private Function ImportCCodes() As Boolean
        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            'routine expects file exported from ICIS to have these column headings
            Dim Fieldnames As String() = {"Limit Sample Type Code", "Limit Sample Type Desc", "State Code"}
            csv = OpenCSV("CCodes")
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\CCodes.txt"
            With csv
                'check that fieldnames are correct...
                Dim ar() As String = .GetFieldHeaders
                For i As Integer = 0 To csv.FieldCount - 1
                    If ar(i) <> Fieldnames(i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                Next

                'create tab-delimited text file suitable for direct import
                sw = New IO.StreamWriter(ImportFilename)
                sw.WriteLine("CCODE\tCCODE_NAME\tDESCRIPTION".Replace("\t", vbTab))
                sw.WriteLine("UNK\tUNKNOWN\tUnknown".Replace("\t", vbTab)) 'include "unknown" code in case data has null value (but don't include description as it shows in report)
                While .ReadNextRecord
                    If .Item(0) <> "" Then
                        sw.WriteLine(String.Format("{0}\t{1}\t{2}", .Item(0), .Item(1), StrConv(.Item(1), VbStrConv.ProperCase)).Replace("\t", vbTab))
                    End If
                End While
            End With
            sw.Close()
            If Not _Project.ImportTable("CCodes", ImportFilename, False) Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "CCodes"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
        End Try
    End Function

    Private Function ImportValidate() As Boolean

        Debug.Assert(lstWaterBodyTypes.Count > 0, "You must import stations before validation")

        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\Validation.txt"

            'routine expects file exported from ICIS to have these column headings
            Dim Fieldnames As String(,) = {{"Statistical Base Code", "Statistical Base Long Desc", "State Code"},
                                           {"Monitoring Location Code", "Monitoring Location Desc", "State Code"}}

            'create tab-delimited text file suitable for direct import
            sw = New IO.StreamWriter(ImportFilename)
            sw.WriteLine("FIELD_NAME\tVALID_VALUE\tDESCRIPTION".Replace("\t", vbTab))

            For f As Integer = 0 To 1
                csv = OpenCSV(Choose(f + 1, "SCodes", "LCodes"))
                With csv
                    'check that fieldnames are correct...
                    Dim ar() As String = .GetFieldHeaders
                    For i As Integer = 0 To csv.FieldCount - 1
                        If ar(i) <> Fieldnames(f, i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                    Next
                    Dim FieldName As String = Choose(f + 1, "SCode", "LCode")
                    While .ReadNextRecord
                        If .Item(0) <> "" Then
                            sw.WriteLine(String.Format("{0}\t{1}\t{2}", FieldName, .Item(0), .Item(1)).Replace("\t", vbTab))
                        End If
                    End While
                    _Project.DB.ExecuteNonQuery($"DELETE FROM {_Project.FullTableName(clsProject.enumTableTypes.Validation)} Validation WHERE Field_Name='{FieldName}'")
                End With
            Next
            For Each s As String In lstWaterBodyTypes
                sw.WriteLine(String.Format("WaterBodyType\t{0}\t{0}", s).Replace("\t", vbTab))
            Next

            sw.Close()
            If Not _Project.ImportTable("Validation", ImportFilename, True) Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "Validation"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
        End Try
    End Function

    ''' <summary>
    ''' This enumeration must correspond to fields downloaded from ICIS
    ''' </summary>
    ''' <remarks></remarks>
    Private Enum EnumData
        NPDES_ID
        Feature_ID
        Version_Num
        End_Date
        Parm_Code
        DMR_Value
        Qual_Code
        NODI_Code
        Limit_Value
        Limit_Value_Type
        Limit_Sample_Type
        Stat_Base_Code
        Mon_Loc_Code
        Limit_Qualif_Code
        Limit_Unit
        Conc_Unit
        Load_Unit
    End Enum

    Private Function ImportData() As Boolean

        Debug.Assert(dictPCodes.Count > 0, "You must import PCodes before data!")

        Dim sw As IO.StreamWriter = Nothing
        Dim csv As LumenWorks.Framework.IO.Csv.CsvReader = Nothing
        Try
            Dim ImportFilename As String = WRDB.Project.clsProject.TempPath & "\Data.txt"
            'routine expects file exported from ICIS to have these column headings
            Dim Fieldnames As String() = {"NPDES ID", "Perm Feature ID", "Version Nmbr", "Monitoring Period End Date", "Parameter Code", "DMR Value", "DMR Value Qualifier Code", "NODI Code", "Limit Value", "Limit Value Type Code", "Limit Sample Type Code", "Statistical Base Code", "Monitoring Location Code", "Limit Value Qualifier Code", "Limit Unit Short Desc", "Std. Concentration Unit Short Desc", "Std. Quantity Unit Short Desc"
}

            RaiseEvent Progress(_Project.DB, 0, 100, "Importing ICIS data to temporary file...")

            'create tab-delimited text file suitable for direct import
            sw = New IO.StreamWriter(ImportFilename)
            sw.WriteLine("STATION_ID\tDATE_TIME\tPCODE\tRESULT\tRCODE\tLEW_PCT\tCCODE\tSCODE\tLCODE\tVALIDATED".Replace("\t", vbTab))

            Dim FileName As String
            If _Rebuild Then
                FileName = "Data_Limits*.csv"
            Else
                FileName = "Data_Limits08-11.csv"
            End If

            'FileName = "Data_Test.csv"

            For Each f As String In My.Computer.FileSystem.GetFiles(_ImportURL, FileIO.SearchOption.SearchTopLevelOnly, FileName)
                Dim NumRecords As Integer
                csv = OpenCSV(IO.Path.GetFileNameWithoutExtension(f), NumRecords)
                With csv
                    'check that fieldnames are correct...
                    Dim ar() As String = .GetFieldHeaders
                    For i As Integer = 0 To csv.FieldCount - 1
                        If ar(i) <> Fieldnames(i) Then Throw New LumenWorks.Framework.IO.Csv.MissingFieldCsvException(String.Format("Invalid field name found in import file: {0}", ar(i)))
                    Next
                    While .ReadNextRecord
                        If .Item(EnumData.NPDES_ID) <> "" And (.Item(EnumData.DMR_Value) <> "" Or .Item(EnumData.NODI_Code) <> "") Then
                            Dim StationID As String = String.Format("{0}{1}-{2}",
                                                                        .Item(EnumData.NPDES_ID),
                                                                        IIf(.Item(EnumData.Feature_ID) = "", "", "+" & .Item(EnumData.Feature_ID)),
                                                                        .Item(EnumData.Version_Num))

                            Dim PCode As String = .Item(EnumData.Parm_Code)
                            Dim PType As String = .Item(EnumData.Limit_Value_Type)
                            Dim Unit As String = .Item(EnumData.Limit_Unit)
                            Dim ConcUnit As String = .Item(EnumData.Conc_Unit)
                            Dim LoadUnit As String = .Item(EnumData.Load_Unit)
                            Dim PCodeUnits As String = dictPCodes(PCode & Unit)

                            'If PType.StartsWith("Q") AndAlso lstPCodes.ContainsKey(PCode & "_LOAD") Then PCode &= "_LOAD"

                            Dim CCode As String = .Item(EnumData.Limit_Sample_Type) 'two character code indicating if composite, grab, etc.

                            'in order for report to work correctly, must always have matching support records; ccode is sometimes missing
                            'only ccodes were observed to have missing (null) values (not scodes or lcodes)
                            If String.IsNullOrEmpty(CCode) Then CCode = "UNK"

                            Dim SCode As String = .Item(EnumData.Stat_Base_Code) 'two character code indicating if min, max, etc.
                            Dim LCode As String = .Item(EnumData.Mon_Loc_Code) 'code indicating monitoring location (influent, effluent, etc.)

                            'could do the following to explicitly indicate whether limit is a max or min, but by observation see that
                            'if SCode description includes the word 'minimum', then and only then the limit is a minimum
                            'therefore, don't dirty up SCode values with appended > or <

                            'If .Item(EnumData.Limit_Qualif_Code) <> "" Then SCode &= .Item(EnumData.Limit_Qualif_Code).Substring(0, 1) 'append < or > to make it easier to determine if limit is max or min allowable value

                            Dim RCode As String = .Item(EnumData.Qual_Code)
                            If RCode = "=" Then RCode = ""
                            If .Item(EnumData.DMR_Value) = "" Then RCode = .Item(EnumData.NODI_Code)

                            sw.WriteLine(String.Format("{0}\t{1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}\t{8}\tTrue",
                                                       StationID,
                                                       .Item(EnumData.End_Date),
                                                       PCodeUnits,
                                                       .Item(EnumData.DMR_Value),
                                                       RCode,
                                                       .Item(EnumData.Limit_Value),
                                                       CCode,
                                                       SCode,
                                                       LCode).Replace("\t", vbTab))
                        End If
                        RaiseEvent Progress(_Project.DB, .CurrentRecordIndex, NumRecords, "Processing ICIS Data record " & .CurrentRecordIndex + 1)
                    End While
                End With
            Next
            sw.Close()
            If _Rebuild Then
                'purge all data records 
                Dim NumRec As Integer = _Project.DB.ExecuteNonQuery("DELETE FROM Master")
                Debug.Assert(NumRec <> -2, "Unable to delete records!")
            Else
                'purge all data records after 1/1/2008
                RaiseEvent Progress(_Project.DB, 50, 100, "Deleting data records since 2008...")
                Dim NumRec As Integer = _Project.DB.ExecuteNonQuery("DELETE FROM Master WHERE Date_Time>={0}", _Project.DB.DateTime(New Date(2008, 1, 1)))
                Debug.Assert(NumRec <> -2, "Unable to delete records!")
            End If
            If Not _Project.ImportTable("Master", ImportFilename, Not _Rebuild, True) AndAlso Not _Project.DB.Cancelled Then Throw New IO.IOException(String.Format("Unable to import {0} to {1} table", ImportFilename, "Master"))
            Return True
        Catch ex As Exception
            Throw
            Return False
        Finally
            If sw IsNot Nothing Then
                sw.Close()
                sw.Dispose()
            End If
            If csv IsNot Nothing Then csv.Dispose()
            RaiseEvent Progress(_Project.DB, 100, 100, "Import is complete.")
        End Try
    End Function

    Public Function ImportICIS() As Boolean
        Return ImportStations() AndAlso ImportPCodes() AndAlso ImportCCodes() AndAlso ImportRCodes() AndAlso ImportValidate() AndAlso ImportData()
    End Function

    Public Function ImportOnlyData() As Boolean
        Return ImportPCodes() AndAlso ImportData()
    End Function

    ''' <summary>
    ''' Create data table formatted like EPD Compliance Unit spreadsheet tables
    ''' One table per station and year, parameters in rows, months in columns
    ''' Include violation summary information to the right for each month
    ''' </summary>
    ''' <param name="ActiveTableName">Name of table to query time series data from</param>
    ''' <param name="Station_ID">Station ID prefix (so can query for all permit versions)</param>
    ''' <param name="Year">Calendar year</param>
    ''' <returns>True if successful</returns>
    ''' <remarks></remarks>
    Public Function OutputReportIcis(ByVal ActiveTableName As String, ByVal Station_ID As String, ByVal Year As Integer) As Boolean
        Dim tempTbl As String = clsProject.TempPrefix & "_IcisReport"
        Try
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn("PCode", GetType(String)))
            dt.Columns.Add(New DataColumn("Anal_Name", GetType(String)))
            dt.Columns.Add(New DataColumn("Units", GetType(String)))
            dt.Columns.Add(New DataColumn("Test_Method", GetType(String)))
            dt.Columns.Add(New DataColumn("LCode", GetType(String)))
            dt.Columns.Add(New DataColumn("LCode_Name", GetType(String)))
            dt.Columns.Add(New DataColumn("CCode", GetType(String)))
            dt.Columns.Add(New DataColumn("CCode_Name", GetType(String)))
            dt.Columns.Add(New DataColumn("SCode", GetType(String)))
            dt.Columns.Add(New DataColumn("SCode_Name", GetType(String)))
            Dim month() As String = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
            For i As Integer = 0 To 11
                dt.Columns.Add(New DataColumn(month(i), GetType(String)))
            Next
            dt.Columns.Add(New DataColumn("Total", GetType(String)))
            dt.Columns.Add(New DataColumn("Avg", GetType(String)))

            For i As Integer = 0 To 11
                dt.Columns.Add(New DataColumn(month(i) & "_Lmt", GetType(String)))
            Next
            For i As Integer = 0 To 11
                dt.Columns.Add(New DataColumn(month(i) & "_Viol", GetType(String)))
            Next

            Dim sqlYear As String = ""
            Select Case _Project.DB.DatabaseType
                Case clsDB.enumDBType.Access, clsDB.enumDBType.Paradox, clsDB.enumDBType.DBase, clsDB.enumDBType.Excel, clsDB.enumDBType.Text, clsDB.enumDBType.SQL_Server, clsDB.enumDBType.SQL_Server_CE
                    sqlYear = String.Format("YEAR(Date_Time)={0}", Year)
                Case clsDB.enumDBType.SQLite
                    sqlYear = String.Format("STRFTIME('%Y',Date_Time)='{0}'", Year)
                Case Else
                    sqlYear = String.Format("EXTRACT(YEAR from Date_Time)={0}", Year)
            End Select

            'Dim sql As String = String.Format("SELECT DISTINCT Dta.PCode, PCodes.Anal_Name, PCodes.Units, PCodes.Test_Method, Dta.LCode, Val_LCode.Description as LCode_Name, Dta.CCode, CCodes.Description, Dta.SCode,Val_SCode.Description as SCode_Name FROM {0} Dta, PCodes, CCodes, Validation Val_LCode, Validation Val_SCode WHERE Dta.Station_ID = '{1}' AND {2} AND Dta.PCode=PCodes.PCode AND Dta.CCode=CCodes.CCode AND Dta.LCode=Val_LCode.Valid_Value AND Val_LCode.Field_Name='LCode' AND Dta.SCode=Val_SCode.Valid_Value AND Val_SCode.Field_Name='SCode' ORDER BY Dta.PCode, Dta.LCode", ActiveTableName, Station_ID, sqlYear)

            'need to query stations using wildcard (so can get all versions of permit), and that is really slow if done like above
            'instead, do two queries, one to extract data, the other to do joins

            Dim sqlStation As String = String.Format("SELECT * FROM {0} WHERE Station_ID LIKE '{1}%' AND {2} ORDER BY PCode, Date_Time", ActiveTableName, Station_ID, sqlYear)

            _Project.DB.DropTable(tempTbl)

            Dim SqlString As String = ""
            Select Case _Project.DB.DatabaseType
                Case clsDB.enumDBType.Paradox, clsDB.enumDBType.Access, clsDB.enumDBType.SQL_Server, clsDB.enumDBType.SQL_Server_CE
                    SqlString = sqlStation.Replace("*", "* INTO " & tempTbl)
                Case clsDB.enumDBType.SQLite, clsDB.enumDBType.MySQL
                    SqlString = String.Format("CREATE TABLE {0} AS {1}", tempTbl, sqlStation)
                Case Else 'these don't support select into queries--could be problems if do ALTER table to update schema and the order of the columns is different
                    _Project.CreateTable(clsProject.enumTableTypes.Temporary, clsProject.TempData)
                    SqlString = String.Format("INSERT INTO {0} {1}", tempTbl, sqlStation)
            End Select

            _Project.DB.ExecuteNonQuery(SqlString)

            'now do join query
            Dim sql As String = String.Format("SELECT DISTINCT Dta.PCode, PCodes.Anal_Name, PCodes.Units, PCodes.Test_Method, Dta.LCode, Val_LCode.Description as LCode_Name, Dta.CCode, CCodes.Description, Dta.SCode,Val_SCode.Description as SCode_Name FROM {0} Dta, PCodes, CCodes, Validation Val_LCode, Validation Val_SCode WHERE Dta.PCode=PCodes.PCode AND Dta.CCode=CCodes.CCode AND Dta.LCode=Val_LCode.Valid_Value AND Val_LCode.Field_Name='LCode' AND Dta.SCode=Val_SCode.Valid_Value AND Val_SCode.Field_Name='SCode' ORDER BY Dta.PCode, Dta.LCode, Dta.CCode, Dta.SCode", tempTbl)

            Dim dr As DbDataReader = _Project.DB.ExecuteReader(sql)
            While dr IsNot Nothing AndAlso dr.Read
                Dim ar(dr.FieldCount - 1) As Object
                dr.GetValues(ar)
                dt.Rows.Add(ar)
            End While
            dr.Close()

            Dim dtaTable As New clsDataTable(_Project, tempTbl)
            Dim lstData As Generic.List(Of clsDataRecord) = dtaTable.SelectRecords()
            For Each row As DataRow In dt.Rows
                Dim sumResult As Double = 0
                Dim numResult As Integer = 0
                For i As Integer = 0 To 11
                    For Each dtaRecord As clsDataRecord In lstData
                        If row.Item("PCode") = dtaRecord.PCode AndAlso row.Item("CCode") = dtaRecord.CCode AndAlso row.Item("LCode") = dtaRecord.LCode AndAlso row.Item("SCode") = dtaRecord.SCode AndAlso CDate(dtaRecord.DateTime).Month = i + 1 Then
                            row.Item(month(i)) = dtaRecord.RCode
                            If dtaRecord.Result <> Single.MinValue Then row.Item(month(i)) &= Math.Round(Val(dtaRecord.Result), 2)
                            If dtaRecord.LEWPct <> Single.MinValue Then row.Item(month(i) & "_Lmt") = dtaRecord.LEWPct
                            Dim PollutantGroup As Integer = TestNull(row.Item("Test_Method"), 0)
                            Dim Violation As String = ""
                            If dtaRecord.Result <> Single.MinValue AndAlso dtaRecord.LEWPct <> Single.MinValue AndAlso dtaRecord.LEWPct > 0 Then
                                Dim ratio As Double = dtaRecord.Result / dtaRecord.LEWPct
                                'need to figure out if limit is a min or a max limit; by observation if SCode name includes 'Minimum' then limit is minimum
                                If CStr(TestNull(row.Item("SCode_Name"), "")).ToUpper.Contains("MINIMUM") Then
                                    If ratio < 1.0 Then Violation = "V"
                                Else
                                    If ratio > 1.0 Then Violation = "V"
                                    'note: also DO and pH are commonly considered to not be subject to TRC violations; hardwire these here
                                    If Not (dtaRecord.PCode = "00300" Or dtaRecord.PCode = "00400") Then
                                        Select Case PollutantGroup
                                            Case 1 'flag as TRC violation if ratio is >= 1.4
                                                If ratio >= 1.4 Then Violation = "T"
                                            Case 2 'flag as TRC violation if ratio is >= 1.2
                                                If ratio >= 1.2 Then Violation = "T"
                                        End Select
                                    End If
                                End If
                            End If
                            row.Item(month(i) & "_Viol") = Violation
                        End If
                    Next
                    If Not IsDBNull(row.Item(month(i))) Then
                        sumResult += Val(TestNull(row.Item(month(i)), 0))
                        numResult += 1
                    End If
                Next
                row.Item("Total") = Math.Round(sumResult, 2)
                row.Item("Avg") = Math.Round(sumResult / IIf(numResult = 0, 1, numResult), 2)
            Next

            If Not _Project.DB.SaveTable(clsProject.TempPrefix & "_RptAnnual", dt) Then Return False

            dt.Clear()
            dt.Dispose()
            dt = Nothing
            Return True
        Catch ex As Exception
            'ErrorMsg(, ex)
            Throw
            Return False
        Finally
            _Project.DB.DropTable(tempTbl)
        End Try
    End Function

    ''' <summary>
    ''' Test object; if dbnull, nothing or empty string, return default value
    ''' </summary>
    Public Function TestNull(ByVal Value As Object, ByVal DefaultValue As Object) As Object
        If IsDBNull(Value) OrElse IsNothing(Value) OrElse (TypeOf Value Is String AndAlso Value = "") Then Return DefaultValue Else Return Value
    End Function

    Private Sub _Project_Progress(ByRef DB As clsDB, Value As Integer, MaxValue As Integer, ProgressText As String) Handles _Project.Progress
        RaiseEvent Progress(_Project.DB, Value, MaxValue, ProgressText)
    End Sub
End Class

